from generate_common import handleValueApply, handleCustomApply

def genI79(ruleDatum, ruleAct, filterData, hsp_abbr):
    # 获得filterData中的 value 值
    value = 180
    for filterDatum in filterData:
        if 'field' in filterDatum and filterDatum['field'] == 'item_num':
            value = filterDatum.get('value', 180)

    # 获得trigger_apply
    triggerApply = ruleAct['trigger_apply']
    # 构造triggerApply的SQL语句
    triggerCodesSql, triggerNamesSql = handleCustomApply(triggerApply, [], type='item', tblAbbr='d')

    # 从 ruleDatum 获得 beginDate, endDate
    beginDate = ruleDatum.get('beginDate', '2020-01-01')
    endDate = ruleDatum.get('endDate', '2099-12-31')

    # 获得 ruleId, ruleActId
    ruleId = ruleDatum['ruleId']
    ruleActId = ruleAct['ruleActId']

    # 构造一个SQL语句
    sql = f"""
WITH t0 AS (
    SELECT d.psn_no
    FROM d
    WHERE 1=1 {triggerCodesSql} {triggerNamesSql}
    GROUP BY d.psn_no
    HAVING SUM(d.q) > 90
),
t1 AS (
    SELECT
        d.*,
        SUM(d.q) OVER (PARTITION BY d.psn_no, d.item_j_code ORDER BY d.item_time_seq) AS accu_q_psn
		FROM d, t0
    WHERE t0.psn_no = d.psn_no
      AND 1=1 {triggerCodesSql} {triggerNamesSql}
      AND d.hsp_abbr = '{hsp_abbr}'
      AND d.b > 0
      AND d.item_time >= TO_DATE('{beginDate}', 'yyyy-mm-dd')
      AND d.item_time <= TO_DATE('{endDate}', 'yyyy-mm-dd')
),
t2 AS (
    SELECT *
    FROM t1
    WHERE t1.accu_q_psn > 90
)
SELECT
	d0_rowid, item_time_seq, setl_id, psn_no, hsp_abbr, j_year, j_isin, 
	item_j_code, item_j_name, item_time, item_day, q, p, c, b,
	'{ruleId}' AS rule_id, '{ruleActId}' AS rule_act_id, '' AS rule_act_msg,
	'该费用发生时，患者累计已使用数量:' || t2.accu_q_psn as related,
    '' as related_rowid,
	LEAST(t2.accu_q_psn - 90, t2.q) as vlt_q,
	t2.p as vlt_p,
	LEAST(t2.accu_q_psn - 90, t2.q) * t2.p as vlt_c,
	LEAST(t2.accu_q_psn - 90, t2.q) * t2.p * ( t2.b / t2.c ) as vlt_b
FROM t2
"""
    
    return sql

def genE32(ruleDatum, ruleAct, filterData, hsp_abbr):
    beginDate = ruleDatum.get('beginDate', '2020-01-01')
    endDate = ruleDatum.get('endDate', '2099-12-31')

    # 直接构造SQL语句
    sql = f"""

with t0 as (
	select d.setl_id, d.item_day
	from d
	where d.item_j_code IN (
        '001203000010000-120300001-3','003106030010000-310603001','001203000010000-120300002S',
        '001203000010000-120300002','001203000010001-120300001-5','001203000010001-120300001-2',
        '003106030020000-310603002E','001203000010100-120300001-1','003106030020000-310603002'
    )
	group by d.setl_id, d.item_day
	having sum(d.q) > 24
)
,t1 as (
	select d.*,
		SUM(d.q) OVER (PARTITION BY d.psn_no, d.item_j_code ORDER BY d.p desc, d.item_time_seq) AS accu_q_tmp
	from d, t0
	where d.setl_id=t0.setl_id and d.item_day = t0.item_day 
        and d.item_j_code IN (
        '001203000010000-120300001-3','003106030010000-310603001','001203000010000-120300002S',
        '001203000010000-120300002','001203000010001-120300001-5','001203000010001-120300001-2',
        '003106030020000-310603002E','001203000010100-120300001-1','003106030020000-310603002'
    )
	order by d.setl_id, d.item_day, d.p desc
)
,t2 as (
	select * from t1 where t1.accu_q_tmp > 24
)
SELECT
	d0_rowid, item_time_seq, setl_id, psn_no, hsp_abbr, j_year, j_isin, 
	item_j_code, item_j_name, item_time, item_day, q, p, c, b,
	'E32' AS rule_id, 'E32_0' AS rule_act_id, '' AS rule_act_msg,
	'该费用发生时，当天累计已使用数量:' || t2.accu_q_tmp as related,
	LEAST(t2.accu_q_tmp - 24, t2.q) as vlt_q,
	t2.p as vlt_p,
	LEAST(t2.accu_q_tmp - 24, t2.q) * t2.p as vlt_c,
	LEAST(t2.accu_q_tmp - 24, t2.q) * t2.p * ( t2.b / t2.c ) as vlt_b
FROM t2
WHERE 1=1
AND item_time >= to_date('{beginDate}', 'yyyy-mm-dd')
AND item_time <= to_date('{endDate}', 'yyyy-mm-dd')
AND hsp_abbr = '{hsp_abbr}'
AND b > 0
"""
    return sql

def genA542(ruleDatum, ruleAct, filterData, hsp_abbr):
    # 从 ruleDatum 获得 beginDate, endDate
    beginDate = ruleDatum.get('beginDate', '2020-01-01')
    endDate = ruleDatum.get('endDate', '2099-12-31')

    # 获得 ruleId, ruleActId
    ruleId = ruleDatum['ruleId']
    ruleActId = ruleAct['ruleActId']

    # 从filterData当中，field是A542的datum
    filterDatum = next((fd for fd in filterData if fd.get('field') == 'A542'), None)

    valueAApply = filterDatum['value_a_apply']
    valueBApply = filterDatum['value_b_apply']

    # 分别处理valueAApply, valueBApply 为 SQL
    valueASql_d, valueANamesSql_d = handleCustomApply(valueAApply, [], type='item', tblAbbr='d')
    valueBSql_d, valueBNamesSql_d = handleCustomApply(valueBApply, [], type='item', tblAbbr='d')
    valueASql_t0, valueANamesSql_t0 = handleCustomApply(valueAApply, [], type='item', tblAbbr='t0')
    valueBSql_t0, valueBNamesSql_t0 = handleCustomApply(valueBApply, [], type='item', tblAbbr='t0')

    # 最终SQL
    sql=f"""
WITH t0 AS (
    SELECT d.setl_id, d.item_day, d.item_j_code, SUM(d.q) AS sum_q
    FROM d
    WHERE 1=1
      AND (
        1=0
        OR (1=1 {valueASql_d} {valueANamesSql_d})
        OR (1=1 {valueBSql_d} {valueBNamesSql_d})
      )
    GROUP BY d.setl_id, d.item_day, d.item_j_code
),
t1 AS (
    SELECT
        t0.setl_id, t0.item_day,
        MAX(CASE WHEN 1=1 {valueASql_t0} {valueANamesSql_t0} THEN t0.sum_q ELSE 0 END) AS a_q,
        SUM(CASE WHEN 1=1 {valueBSql_t0} {valueBNamesSql_t0} THEN t0.sum_q ELSE 0 END) AS b_q
    FROM t0
    GROUP BY t0.setl_id, t0.item_day
    HAVING 
        SUM(CASE WHEN 1=1 {valueBSql_t0} {valueBNamesSql_t0} THEN t0.sum_q ELSE 0 END) > 
        MAX(CASE WHEN 1=1 {valueASql_t0} {valueANamesSql_t0} THEN t0.sum_q ELSE 0 END)
),
t2 AS (
    -- 只取 valueB 的明细，按单价降序排序
    SELECT
        d.*,
        t1.a_q,
        t1.b_q,
        SUM(d.q) OVER (PARTITION BY d.setl_id, d.item_day ORDER BY d.p DESC, d.item_time_seq) AS accu_bq
    FROM d
    JOIN t1 ON d.setl_id = t1.setl_id AND d.item_day = t1.item_day
    WHERE 1=1 {valueBSql_d} {valueBNamesSql_d}
      AND d.item_time >= TO_DATE('{beginDate}', 'yyyy-mm-dd')
      AND d.item_time <= TO_DATE('{endDate}', 'yyyy-mm-dd')
      AND d.hsp_abbr = '{hsp_abbr}'
      AND d.b > 0
),
t3 AS (
    -- 只保留累加数量大于a_q的部分
    SELECT
        t2.*,
        GREATEST(LEAST(accu_bq - a_q, q), 0) AS vlt_q
    FROM t2
    WHERE accu_bq > a_q
)
SELECT
    d0_rowid, item_time_seq, setl_id, psn_no, hsp_abbr, j_year, j_isin, item_j_code, item_j_name, item_time, item_day, q, p, c, b,
    '{ruleId}' AS rule_id, '{ruleActId}' AS rule_act_id, '' AS rule_act_msg,
    '该费用项目发生时，同类项目累计数量达到:' || b_q || '，当天交叉配血数量:' || a_q || '；' AS related,
    vlt_q,
    p AS vlt_p,
    vlt_q * p AS vlt_c,
    vlt_q * p * (b / c) AS vlt_b
FROM t3
WHERE vlt_q > 0
"""
    return sql